package com.arnold.learn.job;

/**
 * Created by arnold.zhu on 2017/7/31.
 */
public class InvokeStatSQL {

    // 当天交易成功笔数
    static final String TODAY_NUMBER = "select count(1) as 'tranum' from kudu_invoices2 where status = '1'" +
            " and DATEDIFF(invoice_date,'2017-08-14') = 0;";

    // 当天交易成功金额
    static final String TODAY_AMOUNT = "select sum(invoice_amt) as 'amount' from kudu_invoices2 where status = '1' " +
            "and DATEDIFF(invoice_date,'2017-08-14') = 0;";

    // 一天销售金额趋势
    static final String TODAY_SALES_TRENDS = "select sum(invoice_amt) as 'amount',t1.hour from  " +
            "(select HOUR(invoice_date) as 'hour',invoice_amt from kudu_invoices2 " +
            "where DATEDIFF(invoice_date,'2017-08-14') = 0 and status = '1' )  t1 group by t1.hour;";

    // 近30天销售金额趋势
    static final String LATEST_30_DAY_SALES_TRENDS = "SELECT sum(invoice_amt) as 'amount',to_date(invoice_date) as 'date' FROM kudu_invoices2 " +
            " WHERE DATEDIFF(invoice_date,'2017-08-14')<0 and DATEDIFF(invoice_date,'2017-08-14')>-31 group by to_date(invoice_date) " +
            "order by to_date(invoice_date) desc;";

    // 错误率最高的十个店
    static final String TOP_10_ERROR_SHOPS = "select count(1) as 'errorcount',store_number from kudu_invoices2 where status = '0' and " +
            "DATEDIFF(invoice_date,'2017-08-14') = 0 group by store_number order by errorCount desc limit 10;";

    // 错误率最高的十种操作
    static final String TOP_10_NUMBER_OF_EXCEPTIONS = "select count(1) as 'errorcount',type,error_code from kudu_invoices2 where status = '0'" +
            " and DATEDIFF(invoice_date,'2017-08-14') = 0 group by type,error_code order by errorcount desc limit 10;";

    // 当天支付方式（成功）统计
    static final String TODAY_PAYMENT_METHOD = "select count(1) as 'count',type from kudu_invoices2 where status = '1' and " +
            "DATEDIFF(invoice_date,'2017-08-14') = 0 group by type;";

    // 当天地区交易成功统计
    static final String TODAY_AREA = "select count(1) as 'count',city from kudu_invoices2,kudu_stores where status = '1' " +
            "and DATEDIFF(invoice_date,'2017-08-14') = 0  group by city;";

}
